﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.Common;

namespace Web.SystemConfig.DataAccess
{
    public class DALProfile : DataAccessBase
    {
        internal DataTable SelectUserInfo(string UserID)
        {
            string strSql = "SELECT * FROM P_Auth_User where P_Auth_User.User_ID= '" + UserID + "'";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }

        internal DataTable SelectRolesByUserID(string UserID)
        {
            string strSql = @" SELECT  
                          P_Auth_Role.Role_ID,  
                          P_Auth_Role.Role_Name
                         FROM  
                          P_Auth_Role
                        where Role_ID in  
                          (select Role_ID from P_Auth_User_Role where User_ID='" + UserID + @"')  
			            or Role_ID in 
			              ( select  Role_ID from P_Auth_ORG_Role where ORG_ID in (select ORG_ID from P_Auth_ORG_User where User_ID = '" + UserID + @"' )) ";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }

        internal DataSet SelectUserAuthoritiesAndModulesAll(string userID)
        {
            string strSql = @"Select distinct Parent_Menu_ID,left(Order_Num,2) as orderNO from  P_Auth_Menu a where a.Parent_Menu_ID in (SELECT dbo.f_GetStr(Menu_ID,2,'.') FROM P_Auth_Function) order by left(Order_Num,2) ";
            DataTable dt = DBHelper.ExecuteDataSet(strSql).Tables[0];
            dt.TableName = "RootNode";
            return dt.DataSet;
        }
        internal DataSet GetRootDS(string ParentModuleID)
        {
            string strSecoSql = "SELECT * FROM P_Auth_Menu where Parent_Menu_ID in(select  distinct Parent_Menu_ID from P_Auth_Menu a where a.Parent_Menu_ID in (SELECT dbo.f_GetStr(Menu_ID,2,'.') FROM P_Auth_Function)) and Menu_ID in  (SELECT dbo.f_GetStr(Menu_ID,3,'.') FROM P_Auth_Function) and Parent_Menu_ID='" + ParentModuleID + "' ORDER BY Parent_Menu_ID,Order_Num";
            return DBHelper.ExecuteDataSet(strSecoSql);
        }

        internal DataSet GetAuthority(string userID, string Menu_ID)
        {
            string strSql;
            if (string.IsNullOrEmpty(userID))
            {
                strSql = "SELECT Func_ID,Menu_ID,Func_Name_ID,cast(0 as bit) as HasAuth,Func_Code FROM P_Auth_Function  WHERE Menu_ID = '" + Menu_ID + "' ORDER BY Func_ID";
            }
            else
            {
                strSql = "SELECT  distinct P_Auth_Function.Func_ID,P_Auth_Function.Menu_ID,P_Auth_Function.Func_Name_ID,Func_Code, " +
            " case when UserAuthority.Func_ID is null then cast(0 as bit) else cast(1 as bit) end as HasAuth " +
            "FROM P_Auth_Function LEFT OUTER JOIN (select Func_ID from P_Auth_Role_Function where Role_ID in(select Role_ID from P_Auth_User_Role where User_ID='" + userID + "') "+
             "or Role_ID in ( select  Role_ID from P_Auth_ORG_Role where ORG_ID in (select ORG_ID from P_Auth_ORG_User where User_ID = '" + userID + "' ))  ) UserAuthority" +
            " ON P_Auth_Function.Func_ID = UserAuthority.Func_ID WHERE P_Auth_Function.Menu_ID = '" + Menu_ID + "' ORDER BY P_Auth_Function.Func_ID";
            }
            return DBHelper.ExecuteDataSet(strSql);
        }

        internal DataSet GetChild(string parentMenu_ID)
        {
            string strSql = "SELECT * FROM P_Auth_Menu WHERE Parent_Menu_ID = '" + parentMenu_ID + "'";
            return DBHelper.ExecuteDataSet(strSql);
        }
    }
}
